require(ggplot2)
require(gridExtra)
require(grid)
require(scales)

# Functions
sqLiteConnect <- function(database, table) {
  require(DBI)
  con <- dbConnect(RSQLite::SQLite(), dbname = database)
  query <- dbSendQuery(con, paste("SELECT * FROM ", table, ";", sep="")) 
  result <- fetch(query, n = -1)
  dbClearResult(query)
  dbDisconnect(con)
  return(result)
}

# Load meetup tables
meetup_user <-  sqLiteConnect("m5s_meetup_jun2015.sqlite", "member")

date_admin_campaign_12 <- as.Date('2012-04-09')
date_admin_elections_12 <- as.Date('2012-05-07')
date_admin_elections_12b <- as.Date('2012-05-21')
date_sicily_election_12 <- as.Date('2012-10-28')
date_firmaday_12 <- as.Date('2012-12-15')
date_gener_election_13 <- as.Date('2013-02-25')

require(dplyr)
meetup_user_daily <- 
  meetup_user %>%
  dplyr::group_by(as.Date(joined)) %>%
  dplyr::summarize(n_joined = n())
names(meetup_user_daily)[1] <- "joined"
meetup_user_daily <- 
  merge(meetup_user_daily, 
        data.frame(joined = seq(from = min(meetup_user_daily$joined, na.rm=TRUE),
                                to = max(meetup_user_daily$joined, na.rm=TRUE),
                                by = 'day')),
        all = TRUE)
meetup_user_daily$n_joined[is.na(meetup_user_daily$n_joined)] <- 0

meetup_user_daily$cumsum <- cumsum(meetup_user_daily$n_joined)

require(zoo)
meetup_user_daily$roll7 <- rollmean(meetup_user_daily$n_joined, 7, fill = NA)
meetup_user_daily$roll30 <- rollmean(meetup_user_daily$n_joined, 30, fill = NA)

# meetup_group_daily <- 
#   meetup_group %>%
#   dplyr::group_by(as.Date(created)) %>%
#   dplyr::summarize(n_created = n())
# names(meetup_group_daily)[1] <- "created"
# meetup_group_daily <- 
#   merge(meetup_group_daily, 
#         data.frame(created = seq(from = min(meetup_group_daily$created),
#                                 to = max(meetup_group_daily$created),
#                                 by = 'day')),
#         all = TRUE)
# meetup_group_daily$n_created[is.na(meetup_group_daily$n_created)] <- 0
# 
# meetup_group_daily$roll7 <- rollmean(meetup_group_daily$n_created, 7, fill = NA)
# meetup_group_daily$roll30 <- rollmean(meetup_group_daily$n_created, 30, fill = NA)
# 
# meetup_rsvps_daily <- 
#   meetup_rsvps %>%
#   dplyr::group_by(as.Date(created)) %>%
#   dplyr::summarize(n_created = n())
# names(meetup_rsvps_daily)[1] <- "created"
# meetup_rsvps_daily <- 
#   merge(meetup_rsvps_daily, 
#         data.frame(created = seq(from = min(meetup_rsvps_daily$created),
#                                  to = max(meetup_rsvps_daily$created),
#                                  by = 'day')),
#         all = TRUE)
# meetup_rsvps_daily$n_created[is.na(meetup_rsvps_daily$n_created)] <- 0
# 
# meetup_rsvps_daily$roll7 <- rollmean(meetup_rsvps_daily$n_created, 7, fill = NA)
# meetup_rsvps_daily$roll30 <- rollmean(meetup_rsvps_daily$n_created, 30, fill = NA)

g_ts <-
  ggplot(meetup_user_daily,
         aes(x=joined, y=n_joined)) +
  geom_vline(xintercept = as.numeric(date_admin_campaign_12), 
             linetype = 'dotted', alpha = 0.4) +
  geom_vline(xintercept = as.numeric(date_admin_elections_12), 
             linetype = 'dashed', alpha = 0.4) +
  geom_vline(xintercept = as.numeric(date_admin_elections_12b), 
             linetype = 'dashed', alpha = 0.4) +
  geom_vline(xintercept = as.numeric(date_sicily_election_12), 
             linetype = 'dashed', alpha = 0.4) +
  geom_vline(xintercept = as.numeric(date_firmaday_12), 
             linetype = 'dotted', alpha = 0.4) +
  geom_vline(xintercept = as.numeric(date_gener_election_13), 
             linetype = 'dashed', alpha = 0.4) +
  geom_bar(stat='identity') +
  geom_line(aes(y=roll30), colour = 'blue') +
  scale_x_date(limits = c(date_admin_elections_12 - 90, 
                          date_gener_election_13 + 60)) +
  labs(x=NULL, y="Daily new members") +
  annotate("text", label = "(1)", y=1000, x=date_admin_campaign_12) +
  annotate("text", label = "(2)", y=1000, x=date_admin_elections_12) +
  annotate("text", label = "(3)", y=1000, x=date_admin_elections_12b) +
  annotate("text", label = "(4)", y=1000, x=date_sicily_election_12) +
  annotate("text", label = "(5)", y=1000, x=date_firmaday_12) +
  annotate("text", label = "(6)", y=1000, x=date_gener_election_13) +
  theme(axis.ticks.x=element_blank(), axis.text.x=element_blank())

g_cum <- 
  ggplot(meetup_user_daily,
         aes(x=joined, y=cumsum)) +
  geom_area(stat='identity', fill = 'gray') +
  geom_vline(xintercept = as.numeric(date_admin_campaign_12), 
             linetype = 'dotted', alpha = 0.4) +
  geom_vline(xintercept = as.numeric(date_admin_elections_12), 
             linetype = 'dashed', alpha = 0.4) +
  geom_vline(xintercept = as.numeric(date_admin_elections_12b), 
             linetype = 'dashed', alpha = 0.4) +
  geom_vline(xintercept = as.numeric(date_sicily_election_12), 
             linetype = 'dashed', alpha = 0.4) +
  geom_vline(xintercept = as.numeric(date_firmaday_12), 
             linetype = 'dotted', alpha = 0.4) +
  geom_vline(xintercept = as.numeric(date_gener_election_13), 
             linetype = 'dashed', alpha = 0.4) +
  scale_x_date(limits = c(date_admin_elections_12 - 90, 
                          date_gener_election_13 + 60)) +
  labs(x=NULL, y="Total members") +
  scale_y_continuous(labels = comma)

# ggplot(meetup_rsvps_daily,
#        aes(x=created, y=n_created)) +
#   geom_vline(xintercept = as.numeric(date_admin_campaign_12), 
#              linetype = 'dotted', alpha = 0.4) +
#   geom_vline(xintercept = as.numeric(date_admin_elections_12), 
#              linetype = 'dotted', alpha = 0.4) +
#   geom_vline(xintercept = as.numeric(date_admin_elections_12b), 
#              linetype = 'dotted', alpha = 0.4) +
#   geom_vline(xintercept = as.numeric(date_sicily_election_12), 
#              linetype = 'dotted', alpha = 0.4) +
#   geom_vline(xintercept = as.numeric(date_firmaday_12), 
#              linetype = 'dotted', alpha = 0.4) +
#   geom_vline(xintercept = as.numeric(date_gener_election_13), 
#              linetype = 'dotted', alpha = 0.4) +
#   geom_bar(stat='identity') +
#   geom_line(aes(y=roll30), colour = 'blue') +
#   scale_x_date(limits = c(date_admin_elections_12 - 90, 
#                           date_gener_election_13 + 60)) +
#   labs(x=NULL, y="Daily new members") +
#   annotate("text", label = "(1)", y=2000, x=date_admin_campaign_12) +
#   annotate("text", label = "(2)", y=2000, x=date_admin_elections_12) +
#   annotate("text", label = "(3)", y=2000, x=date_admin_elections_12b) +
#   annotate("text", label = "(4)", y=2000, x=date_sicily_election_12) +
#   annotate("text", label = "(5)", y=2000, x=date_firmaday_12) +
#   annotate("text", label = "(6)", y=2000, x=date_gener_election_13)

gp1<- ggplot_gtable(ggplot_build(g_ts))
gp2<- ggplot_gtable(ggplot_build(g_cum))
maxWidth = unit.pmax(gp1$widths[2:3], gp2$widths[2:3])
gp1$widths[2:3] <- maxWidth
gp2$widths[2:3] <- maxWidth

grid.arrange(gp1,gp2)
